import pandas as pd
from dmSql import dm_info

tableStr = """

IM_AGENT_USER_APPS_TOP
IM_AGENT_STAT_USER
IM_AGENT_STAT_TOKEN
IM_AGENT_STAT_CHAT_DAY
IM_AGENT_STAT_API_DAY
IM_AGENT_SMART_LABEL
IM_AGENT_SEQUENCE
IM_AGENT_REL_APP_RELEASE
IM_AGENT_REL_PLUGIN_APP
IM_AGENT_REL_PLATFORM
IM_AGENT_PLUGIN_RPA
IM_AGENT_PLUGIN_RELEASE
IM_AGENT_PLUGIN_MACHINE_LOG
IM_AGENT_PLUGIN_FIELD_RELEASE
IM_AGENT_PLUGIN_MACHINE_FIELD
IM_AGENT_PLUGIN_MACHINE
IM_AGENT_PLUGIN_HEADER_RELEASE
IM_AGENT_PLUGIN_HEADER
IM_AGENT_PLUGIN_APP_RELEASE
IM_AGENT_PLUGIN_APP_PLUGINTYPE
IM_AGENT_PLUGIN_APP
IM_AGENT_PLATFORM_INFO
IM_AGENT_OPEN_CALL_STATISTICS
IM_AGENT_OPEN_APPS
IM_AGENT_OPEN_APP_WHITE_LIST
IM_AGENT_OPEN_APP_FILES
IM_AGENT_OPEN_APP_COMPONENTS
IM_AGENT_OPEN_APP_PROGRESS
IM_AGENT_MODEL_APPLICATION
IM_AGENT_MODEL_BASE
IM_AGENT_MODEL_ASSIGN_REL
IM_AGENT_MODEL_ASSIGN
IM_AGENT_MODEL_APPLICATION_REL
IM_AGENT_MODEL_ACCESS_ITEMS
IM_AGENT_MODEL_ACCESS_STRATEGY
IM_AGENT_MODEL_ACCESS_MANAGE
IM_AGENT_MODEL_ACCESS_CONTROL
IM_AGENT_FUNC_LABEL
IM_AGENT_KNOW_CREAT_FLOW_LOG
IM_AGENT_KNOW_CREAT_FLOW
IM_AGENT_MCP_INFO_RELEASE
IM_AGENT_IM_MCP_SERVER_INFO
IM_AGENT_MCP_DETAIL_RELEASE
IM_AGENT_IM_MCP_SERVER_DETAIL
IM_AGENT_HIGH_CODE_TEMPLATE
IM_AGENT_USER_RERANK_REL
IM_AGENT_DATASETS_TOPIC
IM_AGENT_DATASETS_LOG
IM_AGENT_DATABASE_FOLDER
IM_AGENT_DATABASE_FILE_TEMP
IM_AGENT_DATABASE_FILE_DATA
IM_AGENT_DATABASE_FILE
IM_AGENT_DATABASE_FIELD
IM_AGENT_DATABASE
IM_AGENT_CHAT_WORD_CLOUD
IM_AGENT_BI_FILE_INFO
IM_AGENT_BI_CHAT_ITEM_DETAIL
IM_AGENT_BI_CHAT_ITEM
IM_AGENT_FILE_UPLOAD_RECORD
IM_AGENT_FILE_TPL_MAN_CATALOG
IM_AGENT_FILE_TPL_MAN
IM_AGENT_FILE_TPL_FEATURE
IM_AGENT_FILE_CONTENT_FEATURE
IM_AGENT_FILE_CONTENT_CATALOG
IM_AGENT_FILE_TASK_INST
IM_AGENT_FILE_SPLIT_COST
IM_AGENT_FILE_SEGMENT_INFO
IM_AGENT_FILE_CONTENT_INFO

      """


def filter_function(value: str) -> bool:
    # 去除空元素并trim
    tableArr = [item.strip() for item in tableStr.split("\n") if item and item.strip()]
    if len(tableArr) == 0:
        return True
    return tableArr.__contains__(value.split('.')[0])


def get_uid(table_name: str, field_name: str):
    if field_name.__contains__("("):
        return table_name + '.' + field_name.split('(')[1].split(')')[0]
    return table_name + '.' + field_name


def get_modify_sql(row):
    re: str = f"ALTER TABLE \"{row['表代码']}\" MODIFY \"{row['字段代码']}\" {row['数据类型']}({row['数据长度']});"
    return re


def gen_comment_sql(row):
    re: str = f"COMMENT ON COLUMN \"{row['表代码']}\".\"{row['字段代码']}\" IS '{row['字段名']}';"
    return re


if __name__ == '__main__':
    dm_data = dm_info('dm.sql')
    dm_data.to_excel("gen/达梦数据库.xlsx", index=False)

    excel_name = "逻辑模型清单（ 开发组，配合数据标准管控整改，2025-08-28 1710 ）(1)(2)(1).xlsx"
    sheet_name = "逻辑模型清单"
    excel_data = pd.read_excel(excel_name, sheet_name=sheet_name, header=1, dtype={'数据长度': str})
    excel_data = excel_data.drop(["系统名称", "项目名称", "业务域",
                                  "表说明", "表名称", "字段注释", "引用编码标准",
                                  "数据质量要求(有质量要求的一定要填写)", "数据项",
                                  "分册名"
                                  ], axis=1)
    # excel_data['uid'] = excel_data['表代码'] + '.' + excel_data['字段代码']
    excel_data['uid'] = excel_data.apply(lambda row: get_uid(row['表代码'], row['字段代码']), axis=1)

    from dataset_info import read_dataset_info

    ds_info = read_dataset_info("dataset_info.txt")
    excel_data_ds_info = excel_data.merge(ds_info, on='uid', how='left')
    excel_data_ds_info.to_excel("gen/逻辑模型清单.xlsx")

    excel_dm_data = excel_data.merge(dm_data, on='uid', how='outer')
    excel_dm_data = excel_dm_data[excel_dm_data['uid'].apply(filter_function)]
    excel_dm_data.to_excel("gen/高斯达梦_全量字段.xlsx")

    result = excel_dm_data[excel_dm_data['字段代码'].isna() | excel_dm_data['字段DM'].isna()].copy()
    result.to_excel("gen/高斯达梦_未对齐字段.xlsx")

    compare_data = excel_dm_data[excel_dm_data['字段代码'].notna() & excel_dm_data['字段DM'].notna()].copy()
    compare_data['column_type'] = compare_data.apply(lambda row: get_modify_sql(row), axis=1)
    compare_data['column_comm'] = compare_data.apply(lambda row: gen_comment_sql(row), axis=1)
    compare_data.to_excel("gen/高斯达梦_对齐字段.xlsx")

    # 或者更简洁的写法
    with open('gen/修改字段类型和描述.sql', 'w', encoding='utf-8') as f:
        f.write('\n\n'.join((compare_data['column_type'] + "\n" + compare_data['column_comm']).astype(str)))
